
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
from .obj import *

DEFAULT_DB_URL = "sqlite:///all.db"

class GlobalDataBase:

    def __init__(self,url:str=DEFAULT_DB_URL) -> None:
        print(f"init global database {url}")
        global Base
        # self.engine = create_engine(url, echo=True) # echo=True 用于开启日志
        self.engine = create_engine(url)
        Base.metadata.create_all(self.engine)
        self.SessionFunc = sessionmaker(bind=self.engine)
        self.session = None

    def __release(self):
        if self.session:
            self.session.close()
            self.session = None
    
    def Execute(self,sql:str):
        from sqlalchemy.sql import text
        try:
            result = None
            self.__get_session()
            cursor = self.session.execute(text(sql))
            result = cursor.fetchall()
            # with self.engine.connect() as conn:
            #     result_proxy = conn.execute(sql)
            #     result = result_proxy.fetchall()
            return result
        except Exception as e:
            print(f"error in Execute: {str(e)}")
            # self.__rollback()
        return None
    
    def __get_session(self):
        if self.session:
            self.session.close()
            self.session = None
        self.session = self.SessionFunc()
    
    def __commit_and_release(self):
        self.session.commit()
        self.__release()
    
    def __rollback(self):
        self.session.rollback()
    
    # def __query(self,query_sql):
    #     try:
    #         return self.session.query(query_sql)
    #     except Exception as e:
    #         print(f"error in query: {str(e)}")
    #         self.session.rollback()
    #     return None

    def __get_entry_tag_keys(self,entry_id):
        all_keys = set()
        items = self.session.query(Tag).filter_by(EntryId=entry_id).all()
        for item in items:
            all_keys.add(item.Key)
        return sorted(list(all_keys))
    
    def __get_entry_tag_dict(self,entry_id):
        kvs = {}
        items = self.session.query(Tag).filter_by(EntryId=entry_id).all()
        if items:
            for item in items:
                kvs[item.Key] = str(item.Value)
        return kvs
    
    def __del_all_entry_tags(self,entry_id):
        items = self.session.query(Tag).filter_by(EntryId=entry_id).all()
        if items:
            for item in items:
                self.session.delete(item)

    def __del_entry_tag(self,entry_id,key):
        if not isinstance(key,str):
            raise Exception("tag key is not str")
        items = self.session.query(Tag).filter_by(EntryId=entry_id,Key=key).all()
        if items:
            for item in items:
                self.session.delete(item)
    
    def __set_entry_tag(self,entry_id,key,value):
        if not isinstance(key,str):
            raise Exception("tag key is not str")
        if not isinstance(value,str):
            raise Exception("tag value is not str")
        items = self.session.query(Tag).filter_by(EntryId=entry_id,Key=key).all()
        for item in items:
            self.session.delete(item)
        self.session.add(Tag(EntryId=entry_id,Key=key,Value=value))

    def __add_entry_item(self,item:Lancer):
        entry_id = item.EntryId
        # 检查是否注册EntryId
        if self.__has_entry_id(entry_id):
            existing_items = self.session.query(Lancer).filter_by(EntryId=entry_id).all()
            for eitem in existing_items:
                self.session.delete(eitem)
        else:
            self.__add_entry_id_only(entry_id)
        # 添加item
        self.session.add(item)
    
    def __get_entry_ids_of_tag(self,key,value=None):
        items = None
        if value:
            items = self.session.query(Tag).filter_by(Key=key,Value=value).all()
        else:
            items = self.session.query(Tag).filter_by(Key=key).all()
        entry_set = set()
        if items:
            for item in items:
                entry_set.add(item.EntryId)
        return entry_set

    def __get_all_entry_id(self):
        all = set()
        items = self.session.query(Uuid).all()
        if items:
            for item in items:
                all.add(item.EntryId)
        return list(all)
    
    def __get_all_entry_id_of_type(self,typet):
        if not isinstance(typet,int):
            raise Exception("typet is not int")
        all = set()
        items = self.session.query(Uuid).filter_by(Type=typet).all()
        if items:
            for item in items:
                all.add(item.EntryId)
        return list(all)
    
    def __add_entry_id_only(self, entry_id, tihash, idtype=UNKNOWNTYPEID, refs=None):
        if not self.__has_entry_id(entry_id):
            new_entry = Uuid(
                EntryId=entry_id,
                TiHash=tihash,
                Type=idtype,
                Refs=refs
            )
            self.session.add(new_entry)
        else:
            print(f"EntryId {entry_id} already exists in {Uuid.__tablename__}")

    def __del_entry_id_all(self, entry_id):
        # Lancer table
        entries = self.session.query(Lancer).filter_by(EntryId=entry_id).all()
        for entry in entries:
            self.session.delete(entry)
        # Tag table
        entry = self.session.query(Tag).filter_by(EntryId=entry_id).all()
        for entry in entries:
            self.session.delete(entry)
        # Uuid table
        entry = self.session.query(Uuid).filter_by(EntryId=entry_id).first()
        if entry:
            self.session.delete(entry)
        else:
            print(f"EntryId {entry_id} not found in {Uuid.__tablename__}")
    
    def __has_entry_id(self, entry_id):
        exists = self.session.query(Uuid).filter_by(EntryId=entry_id).first() is not None
        return exists
    
    def __get_entry_item(self,entry_id):
        if not self.__has_entry_id(entry_id):
            return None
        item = self.session.query(Lancer).filter_by(EntryId=entry_id).first()
        return item


    """"""

    def HasEntryId(self, entry_id):
        try:
            self.__get_session()
            output = self.__has_entry_id(entry_id)
            self.__release()
            return output
        except Exception as e:
            print(f"error in HasEntryId: {str(e)}")
        return False
    
    def AddEntryIdOnly(self, entry_id, tihash, idtype=UNKNOWNTYPEID, refs=None):
        try:
            self.__get_session()
            self.__add_entry_id_only(entry_id,tihash,idtype,refs)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in AddEntryIdOnly: {str(e)}")
            self.__rollback()
        return False

    def DelEntryIdAll(self, entry_id):
        try:
            self.__get_session()
            self.__del_entry_id_all(entry_id)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in DelEntryIdAll: {str(e)}")
            self.__rollback()
        return False

    def AddEntryItem(self,item:Lancer):
        try:
            self.__get_session()
            self.__add_entry_item(item)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in AddEntryItem: {str(e)}")
            self.__rollback()
        return False
    
    def GetEntryTagKeys(self,entry_id):
        try:
            self.__get_session()
            keylist = self.__get_entry_tag_keys(entry_id)
            self.__release()
            return keylist
        except Exception as e:
            print(f"error in GetEntryTagKeys: {str(e)}")
        return None

    def SetEntryTag(self,entry_id,key,value):
        try:
            self.__get_session()
            self.__set_entry_tag(entry_id,key,value)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in SetEntryTag: {str(e)}")
            self.__rollback()
        return False
    
    def GetEntryItem(self,entry_id):
        try:
            self.__get_session()
            item = self.__get_entry_item(entry_id)
            self.__release()
            return item
        except Exception as e:
            print(f"error in GetEntryItem: {str(e)}")
        return None
    
    def Release(self):
        self.__release()

    def GetEntryTagDict(self,entry_id):
        try:
            self.__get_session()
            item = self.__get_entry_tag_dict(entry_id)
            self.__release()
            return item
        except Exception as e:
            print(f"error in GetEntryTagDict: {str(e)}")
        return None

    def DelAllEntryTags(self,entry_id):
        try:
            self.__get_session()
            self.__del_all_entry_tags(entry_id)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in DelAllEntryTags: {str(e)}")
            self.__rollback()
        return False
 
    def DelEntryTag(self,entry_id,key):
        try:
            self.__get_session()
            self.__del_entry_tag(entry_id,key)
            self.__commit_and_release()
            return True
        except Exception as e:
            print(f"error in DelEntryTag: {str(e)}")
            self.__rollback()
        return False
 
    def GetAllEntryId(self):
        try:
            self.__get_session()
            items = self.__get_all_entry_id()
            self.__release()
            return items
        except Exception as e:
            print(f"error in GetAllEntryId: {str(e)}")
        return None

    def GetAllEntryIdOfType(self,typet):
        try:
            self.__get_session()
            items = self.__get_all_entry_id_of_type(typet)
            self.__release()
            return items
        except Exception as e:
            print(f"error in GetAllEntryIdOfType: {str(e)}")
        return None
    
    def GetEntryIdsOfTag(self,key,value=None):
        try:
            self.__get_session()
            items = self.__get_entry_ids_of_tag(key,value)
            self.__release()
            return items
        except Exception as e:
            print(f"error in GetEntryOfTag: {str(e)}")
        return None

    # def Query(self,query_sql):
    #     try:
    #         self.__get_session()
    #         return self.__query(query_sql)
    #     except Exception as e:
    #         print(f"error in Query: {str(e)}")
    #     return None


GLOABL_DB = None

def ReInitGlobalDB(url:str=DEFAULT_DB_URL):
    global GLOABL_DB
    GLOABL_DB = GlobalDataBase(url)

def DirectSetGlobalDB(db:GlobalDataBase):
    global GLOABL_DB
    if not isinstance(db,GlobalDataBase):
        raise Exception("type error: db is not GlobalDataBase")
    GLOABL_DB = db

def GetGlobalDB():
    global GLOABL_DB
    if GLOABL_DB is None:
        GLOABL_DB = GlobalDataBase(DEFAULT_DB_URL)
    return GLOABL_DB
